package com.hsnn.medstgmini.base.std.dao.impl;

import java.io.OutputStream;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.dao.StdDrugQualitySpecDao;
import com.hsnn.medstgmini.base.std.model.StdDrugQualitySpec;
import com.hsnn.medstgmini.base.std.model.StdDrugQualitySpecLog;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class StdDrugQualitySpecDaoImpl extends BaseHibernateDAO implements StdDrugQualitySpecDao {

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil hibernateSqlUtil;
	
	/**
	 *@category 查询品规库列表
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param Pagination
	 *@return Pagination
	 */
	@SuppressWarnings("unused")
	@Override
	public Pagination getStdDrugQualitySpecList(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT A.qualityspec_id as qualitySpecId, A.qualityspec_name AS qualitySpecName, A.qualityspec_code AS qualitySpecCode, ");
		sql.append(" A.qualityspec_name_spel AS qualitySpecNameSpel,A.form_name AS formName,A.spec_name AS specName,A.status AS status, ");
		sql.append(" A.form AS form,A.form_status AS formStatus,A.spec AS spec,A.form_code AS formCode,A.spec_code AS specCode,A.spec_status AS specStatus");
		sql.append(" FROM std_drugqualityspec A where 1=1 ");
		Map<String,Object> params = new HashMap<String,Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("drugQualitySpecCode"))){
			sql.append(" and A.qualityspec_code = :drugQualitySpecCode");
			params.put("drugQualitySpecCode", page.getConditions().get("drugQualitySpecCode"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("drugQualitySpecName"))){
			sql.append(" and A.qualityspec_name like :drugQualitySpecName");
			params.put("drugQualitySpecName", "%" +page.getConditions().get("drugQualitySpecName")+"%");
		}
		if(StringUtils.isNotBlank(page.getConditions().get("formName"))){
			sql.append(" and A.form_name like :formName");
			params.put("formName", "%"+page.getConditions().get("formName")+"%");
		}
		if(StringUtils.isNotBlank(page.getConditions().get("specName"))){
			sql.append(" and A.spec_name like :specName");
			params.put("specName", "%"+page.getConditions().get("specName")+"%");
		}
		if(StringUtils.isNotBlank(page.getConditions().get("status"))){
			sql.append(" and A.status = :status ");
			params.put("status", page.getConditions().get("status"));
		}
		sql.append(" order by A.qualityspec_code desc ");
		return hibernateSqlUtil.getSqlPageMapList(sql.toString(), params, page);
	}
    
	/**
	 *@category 修改或添加药品品规
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param StdDrugQualitySpec
	 *@return Boolean 保存成功返回true，失败返回false
	 */
	@Override
	public void saveOrAddDrugQualitySpec(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String qualityspecId = page.getConditions().get("qualityspecId");
		String qualityspecName = page.getConditions().get("qualityspecName");
		String qualityspecNameEng = page.getConditions().get("qualityspecNameEng");
		String form = page.getConditions().get("form");
		String formName = page.getConditions().get("formName");
		String spec = page.getConditions().get("spec");
		String specName = page.getConditions().get("specName");
		String updUser = page.getConditions().get("updUser");
		if(StringUtils.isNotBlank(qualityspecId)){
		StringBuffer sql = new StringBuffer();
		sql.append(" update std_drugqualityspec set qualityspec_name = :qualityspecName, ")
		   .append(" qualityspec_name_eng =:qualityspecNameEng,form_name=:formName,spec_name=:specName,  ")   
		   .append(" upd_datetime = :updDatetime, upd_user = :updUser ")
		   .append(" where qualityspec_id = :qualityspecId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("qualityspecId",qualityspecId );
		query.setString("qualityspecName", qualityspecName);
		query.setString("qualityspecNameEng", qualityspecNameEng);
		query.setString("formName", formName);
		query.setString("specName", specName);
		query.setString("updUser",updUser );
		query.setTimestamp("updDatetime", new Date());
		query.executeUpdate();
		session.flush();
		writeLogs("1",qualityspecId,null);
		}else{ 
			String uuid = UUID.randomUUID().toString();
			StringBuffer sql1 = new StringBuffer();
			sql1.append(" insert into std_drugqualityspec (qualityspec_id,qualityspec_code,qualityspec_name,qualityspec_name_eng,form,form_name,spec,spec_name,status,upd_datetime,upd_user) values ")
			   .append("  (:uuid,:qualityspecCode,:qualityspecName,:qualityspecNameEng,:form,:formName,:spec,:specName,1, :updDatetime,:updUser )  ");
			Query query = session.createSQLQuery(sql1.toString());
			query.setString("uuid", uuid);
			query.setString("qualityspecCode", page.getMsg());
			query.setString("qualityspecName",qualityspecName );
			query.setString("qualityspecNameEng", qualityspecNameEng);
			query.setString("form", form);
			query.setString("formName", formName);
			query.setString("spec", spec);
			query.setString("specName", specName);
			query.setTimestamp("updDatetime", new Date());
			query.setString("updUser",updUser );
			query.executeUpdate();
			session.flush();
			writeLogs("1",uuid,null);
		}

	}
	
	/**
	 *@category 修改或添加药品品规
	 *@author 徐国鹏
	 *@date 2015年8月18日08:31:31
	 *@param StdDrugQualitySpec
	 *@return Boolean 保存成功返回true，失败返回false
	 */
	@SuppressWarnings("unchecked")
	@Override
	public int saveOrAddDrugSpec(StdDrugQualitySpec stdDrugQualitySpec) {
		Session session = sessionFactory.getCurrentSession();
		List<String> list = new ArrayList<String>();
		if("0".equals(stdDrugQualitySpec.getStatus()+"")){
			StringBuffer sb = new StringBuffer()
			.append(" select qualityspec_id from std_drugqualityspec where qualityspec_id= :qualityspecId and status <> 0  ");
			Query query = session.createSQLQuery(sb.toString());
			query.setString("qualityspecId", stdDrugQualitySpec.getQualitySpecId());
			List<Object> data = query.list();
			for(int i = 0;i<data.size();i++){
				list.add(data.get(i).toString());
			}
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" update std_drugqualityspec set  ")
		   .append(" status= :status ,upd_datetime= :updDatetime ,upd_user= :updUser  ")
		   .append(" where qualityspec_id = :qualityspecId ");
		Query update = session.createSQLQuery(sql.toString());
		update.setLong("status",stdDrugQualitySpec.getStatus());
		update.setString("updUser", stdDrugQualitySpec.getUpdUser());
		update.setTimestamp("updDatetime", new Date());
		update.setString("qualityspecId", stdDrugQualitySpec.getQualitySpecId());
		int count = update.executeUpdate();
		session.flush();
		writeLogs(stdDrugQualitySpec.getStatus()+"",stdDrugQualitySpec.getQualitySpecId(),list);
		return count;
	}
	/**
	 * 
	 *@category 写日志
	 *@author 徐国鹏
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public void writeLogs(String status, String id,List<String> idList){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer detail = new StringBuffer();
		if(!"0".equals(status)){
			StringBuffer sql1 = new StringBuffer()
			.append(" select  qualityspec_code qualityspecCode,qualityspec_name qualityspecName,qualityspec_name_eng qualityspecNameEng , ")
			.append(" form_name formName  ,spec_name specName ,status status  ")
			.append(" from std_drugqualityspec where qualityspec_id = :id  ")
			.append(" union all ")
			.append(" select t.* from (select qualityspec_code qualityspecCode, qualityspec_name qualityspecName, ")
			.append(" qualityspec_name_eng qualityspecNameEng, form_name formName, spec_name specName, status status  ")
			.append(" from std_drugqualityspec_log where  qualityspec_id = :id order by upd_datetime desc limit 1) t ");
			Map<String,Object> params = new HashMap<String,Object>();
			params.put("id", id);
			List<?> list1 = hibernateSqlUtil.getSqlList(sql1.toString(), params);
			String[] Name = {"药品编码","药品编码","品规英文名","剂型名称","规格名称","状态"};
			if(list1.size()==1){
				Object[] obj = (Object[]) list1.get(0);
				obj[5] = obj[5]==null?"":"1".equals(obj[5].toString())?"启用":"停用";
				for(int i=0;i<obj.length;i++){
					detail.append(";").append(Name[i]).append(":").append(obj[i]==null?"":obj[i].toString());
				}
				detail.delete(0, 1);
			}else{
				Object[] obj1= (Object[]) list1.get(0);//修改后
				Object[] obj2= (Object[]) list1.get(1);//修改前
				obj1[5] = obj1[5]==null?"":"1".equals(obj1[5].toString())?"启用":"停用";
				obj2[5] = obj2[5]==null?"":"1".equals(obj2[5].toString())?"启用":"停用";
				for(int i=0;i<obj2.length;i++){
					String a = obj1[i]==null?"":obj1[i].toString();
					String b = obj2[i]==null?"":obj2[i].toString();
					if(!(a).equals(b)){
						detail.append(";").append(Name[i]).append(":").append(obj2[i]==null?"":obj2[i].toString()).append(",").append(obj1[i]==null?"":obj1[i].toString());
					}
				}
				detail.delete(0, 1);
			}
			
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into std_drugqualityspec_log(log_id,qualityspec_id,qualityspec_code,qualityspec_name,qualityspec_name_spel,qualityspec_name_eng,  ")
		   .append(" form,form_code,form_name,form_status,spec,spec_code,spec_name,spec_status,status,upd_datetime,upd_user,history_qualityspec_code,remark,detail) select ")
		   .append(" UUID(),t.*,:detail from std_drugqualityspec t where t.qualityspec_id= :id ");
		if("0".equals(status)){
			sql.append(" or t.qualityspec_id in (:idList)");
			//字段名,修改后数据,修改前数据
			detail.append("状态,停用,启用");
		}
		Query query=session.createSQLQuery(sql.toString());
		query.setString("id",id);
		if("0".equals(status)){
			query.setParameterList("idList", idList);
		}
		query.setString("detail", detail.toString());
		query.executeUpdate();
		session.flush();
	}
	
	/**
	 *@category 根据id查找药品品规
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param String id
	 *@return StdDrugQualitySpec
	 */
	@Override
	public StdDrugQualitySpec getDrugQualitySpecById(String id) {
		Session session = sessionFactory.openSession();
		StdDrugQualitySpec data = (StdDrugQualitySpec) session.get(StdDrugQualitySpec.class, id);
		session.clear();
		session.close();
		return data;
	}
	
	/**
	 *@category 改变药品品规状态
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param Boolean
	 *@return Boolean 保存成功返回true，失败返回false
	 */
	@Override
	public Boolean changDrugQualitySpecStatus(StdDrugQualitySpec stdDrugQualitySpec) {
		Session session = sessionFactory.getCurrentSession();
		if(null == stdDrugQualitySpec.getQualitySpecId()){
			stdDrugQualitySpec.setQualitySpecId(UUID.randomUUID().toString());
			session.save(stdDrugQualitySpec);
			session.flush();
		}
		if(null != stdDrugQualitySpec.getQualitySpecId()){
			String stdDrugQualitySpecId = stdDrugQualitySpec.getQualitySpecId();
			StdDrugQualitySpec instance = getDrugQualitySpecById(stdDrugQualitySpecId);
			if(null == instance){
				session.save(stdDrugQualitySpec);
				session.flush();
			}else{
				session.update(stdDrugQualitySpec);
				session.flush();
			}
		}
		return true;
		
	}
	/**
	 *@category 获取修改前后状态对比
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param void
	 *@return List<StdDrugQualitySpec> 
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<StdDrugQualitySpecLog> getCompareStatus(String updDatetime, String id) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select A.* from std_drugqualityspec_log A WHERE A.qualityspec_id =:id AND A.upd_datetime <=:updDatetime order by A.upd_datetime DESC limit 2");
		Query query = session.createSQLQuery(sql.toString()).addEntity(StdDrugQualitySpecLog.class);
		query.setParameter("id", id);
		query.setParameter("updDatetime", updDatetime);
	    List<StdDrugQualitySpecLog> list = (List<StdDrugQualitySpecLog>)query.list();
		return list;
	}
	/**
	 *@category 获取日志列表展示页面
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param void
	 *@return List<StdDrugQualitySpec> 
	 */
	@SuppressWarnings("unused")
	@Override
	public Pagination getStdDrugQualitySpecLogList(Pagination page,StdDrugQualitySpec stdDrugQualitySpec) {//StdDrugQualitySpecLog
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select A.log_id as logId, A.qualityspec_id as qualitySpecId, A.qualityspec_name AS qualitySpecName, A.qualityspec_code AS qualitySpecCode, ");
		sql.append(" A.qualityspec_name_spel AS qualitySpecNameSpel,A.form_code AS formCode,A.form_name AS formName,A.form_status AS formStatus,A.form AS form, ");
		sql.append(" A.spec AS spec,A.spec_code AS specCode,A.spec_status AS specStatus,A.spec_name AS specName,A.`status` AS `status`, ");
		sql.append(" s.name AS updUser,date_format(A.upd_datetime,'%Y-%m-%d %H:%i:%S') AS updDatetime,A.detail AS detail ");
		sql.append(" FROM std_drugqualityspec_log A ");
		sql.append(" left join sys_user s on s.id=A.upd_user ");
		sql.append("  where 1=1 ");
		Map<String,Object> params = new HashMap<String,Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("startPoint"))){
			Date startDate = DateUtil.parseDateTime(page.getConditions().get("startPoint"));
			Timestamp startTimestamp = DateUtil.getTimeStamp(startDate);
			sql.append(" and A.upd_datetime > :startTimestamp");
			params.put("startTimestamp", startTimestamp);
		}
		if(StringUtils.isNotBlank(page.getConditions().get("endPoint"))){
			Date endDate = DateUtil.parseDateTime(page.getConditions().get("endPoint"));
			Timestamp endTimestamp = DateUtil.getTimeStamp(endDate);
			sql.append(" and A.upd_datetime < :endTimestamp");
			params.put("endTimestamp", endTimestamp);
		}
		if(StringUtils.isNotBlank(page.getConditions().get("qualitySpecName"))){
			sql.append(" and A.qualityspec_name like :qualitySpecName");
			params.put("qualitySpecName", page.getConditions().get("qualitySpecName"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("formName"))){
			sql.append(" and A.form_name like :formName");
			params.put("formName", page.getConditions().get("formName"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("specName"))){
			sql.append(" and A.spec_name like :specName");
			params.put("specName", page.getConditions().get("specName"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("qualitySpecId"))){
			sql.append(" and A.qualityspec_id = :qualitySpecId");
			params.put("qualitySpecId", page.getConditions().get("qualitySpecId"));
		}
		sql.append(" order by A.upd_datetime DESC");
		return hibernateSqlUtil.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 *@category 保存或新增品规库日志
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param void
	 *@return List<StdDrugQualitySpec> 
	 */
	@Override
	public Boolean saveOrAddDrugQualitySpecLog(
			StdDrugQualitySpecLog stdDrugQualitySpecLog) {
		Session session = sessionFactory.getCurrentSession();
		if(null == stdDrugQualitySpecLog.getLogId()){
			stdDrugQualitySpecLog.setLogId(UUID.randomUUID().toString());
			session.save(stdDrugQualitySpecLog);
			session.flush();;
		}
		if(null != stdDrugQualitySpecLog.getLogId()){
			String logId = stdDrugQualitySpecLog.getLogId();
			StdDrugQualitySpec instance = getStdDrugQualitySpecLogById(logId);
			if(null == instance){
				session.save(stdDrugQualitySpecLog);
				session.flush();
			}else{
				session.update(stdDrugQualitySpecLog);
				session.flush();
			}
		}
		return true;
	}
	/**
	 *@category 根据Id查找品规日志
	 *@author zhouqi
	 *@date 2015年8月18日08:31:31
	 *@param void
	 *@return List<StdDrugQualitySpec> 
	 */
	@Override
	public StdDrugQualitySpec getStdDrugQualitySpecLogById(String logId) {
		Session session = sessionFactory.getCurrentSession();
		StdDrugQualitySpec stdDrugQualitySpec = (StdDrugQualitySpec) session.get(StdDrugQualitySpec.class, logId);
		return stdDrugQualitySpec;
	}

	/**
	 * 品规库维护记录详情
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getStdDrugQualitySpecLogDetail(StdDrugQualitySpecLog stdDrugQualitySpecLog) {
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT s.qualityspec_id qualitySpecId,s.qualityspec_name qualitySpecName,s.qualityspec_code qualitySpecCode,  ")
		   .append(" s.qualityspec_name_spel qualitySpecNameSpel,s.qualityspec_name_eng qualitySpecNameEng, s.form_name formName,s.spec_name specName, ")
		   .append(" DATE_FORMAT(s.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime,b.name updUser, s.status status  ")
		   .append(" FROM std_drugqualityspec_log s  left join sys_user b on b.id=s.upd_user ")
		   .append(" WHERE s.qualityspec_id = (SELECT qualityspec_id FROM std_drugqualityspec_log WHERE log_id = :logId)  ")
		   .append(" AND s.upd_datetime <= (SELECT upd_datetime FROM std_drugqualityspec_log where log_id = :logId)   ")
		   .append(" order by s.upd_datetime desc limit 2 ");
		Map<String,String> params = new HashMap<String,String>();
		params.put("logId", stdDrugQualitySpecLog.getLogId());
		List<Map<String,Object>> list = (List<Map<String,Object>>) hibernateSqlUtil.getSqlMapList(sql.toString(), params);
		return list;
	}
	
	/**
	 * 
	 * @category 品规库 新增编辑  剂型 弹窗
	 * @author 言科
	 * @date 2015年9月25日 String
	 */
	@SuppressWarnings("unused")
	public Pagination getStdDrugQualitySpecDrugForm(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT form_id form, form_code formCode, form_name formName, status  ")
		   .append(" FROM  dic_drugform  WHERE status = 1 and 1=1 ");
		Map<String,Object> params = new HashMap<String,Object>();
		
		if(StringUtils.isNotBlank(page.getConditions().get("formCode"))){
			sql.append(" and form_code = :formCode");
			params.put("formCode", page.getConditions().get("formCode"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("formName"))){
			sql.append(" and form_name like :formName");
			params.put("formName", "%"+page.getConditions().get("formName")+"%");
		}
		return hibernateSqlUtil.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 品规库 新增编辑  规格 弹窗
	 * @author 言科
	 * @date 2015年9月25日 String
	 */
	@SuppressWarnings("unused")
	public Pagination getStdDrugQualitySpecDrugSpec(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT spec_id spec, spec_code specCode, spec_name specName, status   ")
		   .append(" FROM  dic_drugspec WHERE status = 1 and 1=1 ");
		Map<String,Object> params = new HashMap<String,Object>();
		
		if(StringUtils.isNotBlank(page.getConditions().get("specCode"))){
			sql.append(" and spec_code = :specCode");
			params.put("specCode", page.getConditions().get("specCode"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("specName"))){
			sql.append(" and spec_name like :specName");
			params.put("specName","%"+ page.getConditions().get("specName")+"%");
		}
		return hibernateSqlUtil.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 * @category 校验编码的重复性
	 * @author zhouqi
	 * @date 2015年9月25日 
	 */
    @SuppressWarnings("unchecked")
	@Override
	public List<StdDrugQualitySpec> validRep(StdDrugQualitySpec stdDrugQualitySpec){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from std_drugqualityspec A where 1=1");
		if(StringUtils.isNotBlank(stdDrugQualitySpec.getQualitySpecCode())){
			sql.append(" AND A.qualityspec_code = :qualitySpecCode");
		}
		if(StringUtils.isNotBlank(stdDrugQualitySpec.getQualitySpecId())){
			sql.append(" AND A.qualityspec_id <> :qualiySpecId");
		}
		Query query = session.createSQLQuery(sql.toString()).addEntity(StdDrugQualitySpec.class);
		if(StringUtils.isNotBlank(stdDrugQualitySpec.getQualitySpecCode())){
			query.setParameter("qualitySpecCode", stdDrugQualitySpec.getQualitySpecCode());
		}
		if(StringUtils.isNotBlank(stdDrugQualitySpec.getQualitySpecId())){
			sql.append(" AND A.qualityspec_id <> :qualiySpecId");
			query.setParameter("qualiySpecId", stdDrugQualitySpec.getQualitySpecId());
		}
		return query.list();
	}
    
    @Override
	public void exportExcel(Pagination page,OutputStream os){
        XSSFWorkbook wb = new XSSFWorkbook();  
        XSSFSheet sheet = wb.createSheet("品规");  
        XSSFRow row = sheet.createRow((int) 0);  
        XSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); 
        
        XSSFCell cell = null;
        String[] headers = {"品规编码","品规名称","剂型名称","规格名称","状态"};
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Object> list = getFormNoPage(page);
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Object[] obj = (Object[]) list.get(i);  
            for(int j = 0;j<obj.length;j++){
            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
            }
            
        }  
        try {   
            wb.write(os);  
            os.close();  
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
            e.printStackTrace();  
        } 
       
	}

	@SuppressWarnings("unchecked")
	private List<Object> getFormNoPage(Pagination page){
		String qualityspecCode = page.getConditions().get("qualityspecCode");
		String qualityspecName = page.getConditions().get("qualityspecName");
		String formName = page.getConditions().get("formName");
		String specName = page.getConditions().get("specName");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer()
		.append(" select  qualityspec_code qualityspecCode, qualityspec_name qualityspecName,form_name formName,spec_name specName,case status when '1' then '启用' when '2' then '停用' end  status ")
		.append(" from std_drugqualityspec WHERE 1=1 ");
		if (StringUtils.isNotBlank(qualityspecCode)) {
			sql.append(" and qualityspec_code = :qualityspecCode ");
		}
		if (StringUtils.isNotBlank(qualityspecName)) {
			sql.append(" and qualityspec_name like :qualityspecName ");
		}
		if (StringUtils.isNotBlank(formName)) {
			sql.append(" and form_name like :formName ");
		}
		if (StringUtils.isNotBlank(specName)) {
			sql.append(" and spec_name like :specName ");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" and status = :status ");
		}

		Query query = getSession().createSQLQuery(sql.toString());
		if (StringUtils.isNotBlank(qualityspecCode)) {
			query.setParameter("qualityspecCode",  qualityspecCode );
		}
		if (StringUtils.isNotBlank(qualityspecName)) {
			query.setParameter("qualityspecName", "%" + qualityspecName + "%");
		}
		if (StringUtils.isNotBlank(formName)) {
			query.setParameter("formName", "%" + formName + "%");
		}
		if (StringUtils.isNotBlank(specName)) {
			query.setParameter("specName", "%" + specName + "%" );
		}
		if (StringUtils.isNotBlank(status)) {
			query.setParameter("status", status);
		}
		return query.list();
	}
	public List<Map<String, Object>> getQualityspec(String drugName, String formName, String specName){
		Session session = sessionFactory.openSession();
		StringBuffer sql = new StringBuffer()
		.append("select * from std_drugqualityspec a")
		.append(" where 1=1");
		Map<String, Object> params = new HashMap<String, Object>();
		if (StringUtils.isNotBlank(drugName)){
			sql.append(" and a.qualityspec_name=:qName");
			params.put("qName", drugName);
		}
		if (StringUtils.isNotBlank(formName)){
			sql.append(" and a.form_name=:formName");
			params.put("formName", formName);
		}
		if (StringUtils.isNotBlank(specName)){
			sql.append(" and a.spec_name=:specName");
			params.put("specName", specName);
		}
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		return list;
	}
}
